﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Qu.SeniorProject.Model
{
    public class GradingRepository
    {
        public static List<CriteriaRating> GetRatings(bool IsForGrading)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.CriteriaRatings.Where(c => c.IsForGrading == IsForGrading).ToList();
        }

        public static List<CriteriaCategory> GetCriteriaCategories(string gradingType, int projectID, int facultyID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<CriteriaCategory>("GetCriteriaCategories @gradingType, @projectID, @facultyID",
                new SqlParameter("gradingType", gradingType),
                new SqlParameter("projectID", projectID),
                new SqlParameter("facultyID", facultyID)).ToList();
        }

        public static List<StudentGrade> GetStudentGrades(string studentID, string gradingType)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<StudentGrade>("GetStudentGrades @studentID, @gradingType",
                new SqlParameter("studentID", studentID),
                new SqlParameter("gradingType", gradingType)).ToList();
        }


        public static List<StudentGrade> GetProjectGrades(int projectID, string studentID, string gradingType, string examinerRole)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<StudentGrade>("GetProjectGrades @projectID, @studentID, @gradingType, @examinerRole",
                new SqlParameter("projectID", projectID),
                new SqlParameter("studentID", studentID),
                new SqlParameter("gradingType", gradingType),
                new SqlParameter("examinerRole", (examinerRole == null ? "" : examinerRole))).ToList();
        }

        public static List<StudetGradeDetail> GetStudentGradeDetails(int projectID, string studentID, string gradingType, string examinerRole)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<StudetGradeDetail>("GetStudentGradeDetails @projectID, @studentID, @gradingType, @examinerRole",
                new SqlParameter("projectID", projectID),
                new SqlParameter("studentID", studentID),
                new SqlParameter("gradingType", gradingType),
                new SqlParameter("examinerRole", (examinerRole == null ? "" : examinerRole))).ToList();
        }

        public static List<StudetGradeDetail> GetStudentGradeSummary(int projectID, string studentID, string gradingType)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<StudetGradeDetail>("GetStudentGradeSummary @projectID, @studentID, @gradingType",
                new SqlParameter("projectID", projectID),
                new SqlParameter("studentID", studentID),
                new SqlParameter("gradingType", gradingType)).ToList();
        }

        public static List<StudentGrade> GetAllGrades()
        {
            var spDB = new SeniorProjectEntities();
            var result = spDB.Database.SqlQuery<StudentGrade>("GetAllGrades");

            try
            {
                return result.ToList();
            }
            catch (Exception)
            {
                
               return new List<StudentGrade>();
            }
        }

        //public static List<Criterion> GetCriteria(int categoryID)
        //{
        //    var spDB = new SeniorProjectEntities();
        //    return spDB.Criteria.Where(c=> c.CategoryID == categoryID).ToList();
        //}

        public static List<EvaluationDetail> GetProjectEvalautionDetails(int projectID, int facultyID, int evaluationID, string gradingType)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<EvaluationDetail>("GetProjectEvalautionDetails @projectID, @facultyID, @evaluationID, @gradingType",
                new SqlParameter("projectID", projectID),
                new SqlParameter("facultyID", facultyID),
                new SqlParameter("evaluationID", evaluationID),
                new SqlParameter("gradingType", gradingType)).ToList();
        }

        public static int DeleteProjectEvaluationDetails(int evaluationID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.ExecuteSqlCommand("DeleteProjectEvaluationDetails @evaluationID",
                 new SqlParameter("evaluationID", evaluationID));
        }

        public static ProjectEvaluation GetProjectEvaluation(int projectID, int facultyID, string gradingType)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<ProjectEvaluation>("GetProjectEvaluation @projectID, @facultyID, @gradingType",
                new SqlParameter("projectID", projectID),
                new SqlParameter("facultyID", facultyID),
                new SqlParameter("gradingType", gradingType)).FirstOrDefault();
        }

        public static void SaveProjectEvaluation(int? evaluationID, string gradingType, int projectID, int facultyID, string overallComment, List<ProjectEvaluationDetail> evaluationsDetails)
        {
            ProjectEvaluation projectEvaluation = null;
            var spDB = new SeniorProjectEntities();

            if (evaluationID == null)
            {
                projectEvaluation = new ProjectEvaluation
                {
                    ProjectID = projectID,
                    ExaminerID = facultyID,
                    EvaluationDate = DateTime.Now,
                    Comments = overallComment,
                    EvaluationType = gradingType
                };

                spDB.ProjectEvaluations.Add(projectEvaluation);
            }
            else
            {
                projectEvaluation = spDB.ProjectEvaluations.Find(evaluationID.Value);
                projectEvaluation.ExaminerID = facultyID;
                projectEvaluation.EvaluationDate = DateTime.Now;
                projectEvaluation.Comments = overallComment;

                DeleteProjectEvaluationDetails(evaluationID.Value);
            }
            //spDB.SaveChanges();

            foreach (var evaluationDetail in evaluationsDetails)
            {
                //if (evaluationDetail.StudentID == null)
                //    evaluationDetail.StudentID = string.Empty; //ToDo: fix - this is just a workaround
                projectEvaluation.ProjectEvaluationDetails.Add(evaluationDetail);
            }
            //{
            //    evaluationDetail.EvaluationID = projectEvaluation.EvaluationID;
            //    spDB.ProjectEvaluationDetails.Add(evaluationDetail);
            //}

            spDB.SaveChanges();
        }

        public static int AddEvaluationOfAddressingFeedback(int projectId, int ratingId, string comments)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.ExecuteSqlCommand("AddEvaluationOfAddressingFeedback @projectID, @RatingId, @Comments", 
                 new SqlParameter("projectID", projectId),
                 new SqlParameter("RatingId", ratingId),
                 new SqlParameter("Comments", comments));
        }

    }
}
